<?php
declare (strict_types = 1);

namespace app\command;

use app\model\Flow;
use app\model\Hotel;
use app\service\FlowService;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\console\Command;
use think\console\Input;
use think\console\Output;

class Init extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('app\command\init')
            ->setDescription('初始化酒店到数据库');
    }

    protected function execute(Input $input, Output $output)
    {
        $output->writeln('开始载入文件');
        $file = [
            '大理镇_202411.xlsx',
            '凤仪镇_202411.xlsx',
            '海东镇_202411.xlsx',
            '满江街道_202411.xlsx',
//            '上关镇_202411.xlsx',
//            '双廊镇_202411.xlsx',
//            '太和街道_202411.xlsx',
//            '挖色镇_202411.xlsx',
//            '湾桥镇_202411.xlsx',
//            '喜洲镇_202411.xlsx',
//            '下关街道_202411.xlsx',
//            '银桥镇_202411.xlsx'
        ];
        $sql = '';
        //处理文件（当前只处理前6个表）
        foreach ($file as $name) {
            $filename = public_path().'data/'.$name;
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filename);
            //循环读取所有的sheet
            //常驻人口结构
            $resident_population =  ['town','personnel_type','month_value','last_three_month_value','last_six_month_value','last_twelve_month_value','month','year'];
            //常驻人口画像
            $population_portrait =  ['town','personnel_type','tag_category','tag_name','attr','value','tgi','month','year'];
            //客流指数
            $flow_index =  ['town','type','time_type','result_granularity','time_attr','month_value','last_three_month_value','last_six_month_value','last_twelve_month_value','month','year'];
            //客流画像
            $passenger_flow_portrait =  ['town','personnel_type','tag_category','tag_name','attr','value','tgi','month','year'];
            //业态分布
            $business_distribution =  ['town','profession','business_name','value','month','year'];
            //垂直业态分布
            $vertical_business_distribution =  ['town','poi_category','level','value','month','year'];
            //交通设施
            $traffic_facilities = ['town','type','data_item','value','month','year'];
            //消费指数
            $consumption_index = ['town','fist_class','second_class','capita_price','tgi','month','year'];
            //热力图
            $hotmap = ['town','type','coordinate','weight','month','year'];
            //竞品分析
            $competitive_product_analysis = ['town','brand','store','address','popularity','month','year'];
            //常驻人口结构
            $sheet = $spreadsheet->getSheetByName('常驻人口结构');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                $values[] = $sheet->getCell('G' . $i)->getValue();
                $values[] = $sheet->getCell('H' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$resident_population);
                $sql .= "INSERT INTO tencent_lbs_resident_population ($column) VALUES {$totalValues};";
            }
            //常驻人口画像
            $sheet = $spreadsheet->getSheetByName('常驻人口画像');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                $values[] = $sheet->getCell('G' . $i)->getValue();
                $values[] = $sheet->getCell('H' . $i)->getValue();
                $values[] = $sheet->getCell('I' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$population_portrait);
                $sql .= "INSERT INTO tencent_lbs_population_portrait ($column) VALUES {$totalValues};";
            }
            //客流指数
            $sheet = $spreadsheet->getSheetByName('客流指数');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                $values[] = $sheet->getCell('G' . $i)->getValue();
                $values[] = $sheet->getCell('H' . $i)->getValue();
                $values[] = $sheet->getCell('I' . $i)->getValue();
                $values[] = $sheet->getCell('J' . $i)->getValue();
                $values[] = $sheet->getCell('K' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$flow_index);
                $sql .= "INSERT INTO tencent_lbs_passenger_flow_index ($column) VALUES {$totalValues};";
            }
            //客流画像
            $sheet = $spreadsheet->getSheetByName('客流画像');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                $values[] = $sheet->getCell('G' . $i)->getValue();
                $values[] = $sheet->getCell('H' . $i)->getValue();
                $values[] = $sheet->getCell('I' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = "''";
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$passenger_flow_portrait);
                $sql .= "INSERT INTO tencent_lbs_passenger_flow_portrait ($column) VALUES {$totalValues};";
            }
            //业态分布
            $sheet = $spreadsheet->getSheetByName('业态分布');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$business_distribution);
                $sql .= "INSERT INTO tencent_lbs_business_distribution ($column) VALUES {$totalValues};";
            }
            //垂直业态分布
            $sheet = $spreadsheet->getSheetByName('垂类业态分布');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$vertical_business_distribution);
                $sql .= "INSERT INTO tencent_lbs_vertical_business_distribution ($column) VALUES {$totalValues};";
            }
            //交通设施
            $sheet = $spreadsheet->getSheetByName('交通设施');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$traffic_facilities);
                $sql .= "INSERT INTO tencent_lbs_traffic_facilities ($column) VALUES {$totalValues};";
            }
            //消费指数
            $sheet = $spreadsheet->getSheetByName('消费指数');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                $values[] = $sheet->getCell('G' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$consumption_index);
                $sql .= "INSERT INTO tencent_lbs_consumption_index ($column) VALUES {$totalValues};";
            }
            //热力图
            $sheet = $spreadsheet->getSheetByName('热力图');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$hotmap);
                $sql .= "INSERT INTO tencent_lbs_hotmap ($column) VALUES {$totalValues};";
            }
            //竞品分析
            $sheet = $spreadsheet->getSheetByName('竞品分析');
            $rows  = $sheet->getHighestRow();
            $totalValues = [];
            for ($i = 2; $i <= $rows; $i++) {
                //如果首行为空那么就肯定结束了
                if (empty($sheet->getCell('A' . $i)->getValue())) {
                    continue;
                }
                $values[] = $sheet->getCell('A' . $i)->getValue();
                $values[] = $sheet->getCell('B' . $i)->getValue();
                $values[] = $sheet->getCell('C' . $i)->getValue();
                $values[] = $sheet->getCell('D' . $i)->getValue();
                $values[] = $sheet->getCell('E' . $i)->getValue();
                $values[] = $sheet->getCell('F' . $i)->getValue();
                $values[] = $sheet->getCell('G' . $i)->getValue();
                foreach ($values as &$item) {
                    if ($item !== '' && !is_null($item)) {
                        $item = "'{$item}'";
                    }else {
                        $item = 'null';
                    }
                }
                $res = implode(',',$values);
                $totalValues[] = '(' . $res . ')';
                $values = [];
            }
            if (!empty($totalValues)) {
                $totalValues = implode(',',$totalValues);
                $column = implode(',',$competitive_product_analysis);
                $sql .= "INSERT INTO tencent_lbs_competitive_product_analysis ($column) VALUES {$totalValues};";
            }
            unset($spreadsheet);
        }
        trace("全部sql： {$sql}", 'error');
    }
}
